package com.huangpuweb.api.service.imp;

import com.huangpuweb.api.model.*;
import com.huangpuweb.api.service.CrowdSituationApiService;
import com.huangpuweb.api.util.DateUtil;
import com.huangpuweb.api.util.DateUtils;
import com.huangpuweb.api.util.StringUtil;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Service
public class CrowdSituationApiServiceImp extends BaseSysDataServiceImp implements CrowdSituationApiService {


    @Override
    public SysRefinedCrowdSituation getAllDataRecordBack(String s) {

        String sql = "select spotName,spotCode,realtimeNumber,totalNumber,createTime from sys_refined_crowdSituation where spotCode =? order  by createTime desc LIMIT 0 ,1";
        return getEntry(sql, new RowMapper<SysRefinedCrowdSituation>() {
            @Override
            public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                SysRefinedCrowdSituation sc=new SysRefinedCrowdSituation();
                sc.setSpotName(resultSet.getString("spotName"));
                sc.setSpotCode(resultSet.getString("spotCode"));
                sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                sc.setTotalNumber(resultSet.getInt("totalNumber"));
                sc.setCreateTime(resultSet.getString("createTime"));
                return sc;
            }
        }, new Object[]{s});
    }

    @Override
    public SysRefinedCrowdSituation getMaxNumberRecord(String todayZero, String s) {
        String sql = "select *  from sys_refined_crowdSituation where spotcode=?  and createTime >=? order by realtimeNumber desc limit 0,1";
        return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{s, todayZero});
    }

    @Override
    public SysHistoryCrowdSituation getYearAndMonthRecord(String s, String recordTime) {
        String sql = "select * from sys_history_crowdSituation where spotCode=? and  recordTime=?";
        return getEntry(sql, new SysHistoryCrowdSituationRowMapper(), new Object[]{s, recordTime});
    }

    @Override
    public CrowdSysSetting getCrowdSysSettingBySpotCode(String s) {
        String sql = "select * from crowd_sys_setting where spotcode=?";
        return getEntry(sql, new CrowdSysSettingRowMapper(), new Object[]{s});
    }

    @Override
    public SysHistoryCrowdSituation getSysHistoryCrowdSituationBySpotCode(String spotCode, int i) {
        String sql = "select * from sys_history_crowdSituation where spotCode= ?  order by recordTime desc limit ?,?";
        return getEntry(sql, new SysHistoryCrowdSituationRowMapper(), new Object[]{spotCode, i, 1});
    }

    @Override
    public List<SysRefinedCrowdSituation> getListSysRefinedCrowdSituationByEveryHour(String todayZero, String today23, String s) {
        String sql = "select * from sys_refined_crowdSituation where spotCode=? and createTime <=? and createTime >=? ORDER BY createTime ASC";
        return getEntryList(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{s, today23, todayZero});
    }

    @Override
    public HpSubwayExitInfo getHpSubwayExitInfo(String subwayname, String spotCode) {
//        String sql="select * from hp_subway_exit_sys where spotCode= ?  and  subwayId=? order by id desc";
//        return getEntry(sql,new HpSubWayExitInfoRowMapper(),new Object[]{spotCode,subwayId});
        String sql = "select * from hp_subway_exit_sys where spotCode= ? and zdmc=? order by id desc";
        return getEntry(sql, new HpSubWayExitInfoRowMapper(), new Object[]{spotCode, subwayname});
    }

    @Override
    public SysHpParkInfo getSysHpParkInfo(String parkId) {
        String sql = "select id,parkId,total,total_left,parkingNumber,recordTime,updateTime from hp_park_info where parkId=? order by recordTime limit 0,1";
        return getEntry(sql, new RowMapper<SysHpParkInfo>() {
            @Override
            public SysHpParkInfo mapRow(ResultSet rs, int i) throws SQLException {
                SysHpParkInfo lj = new SysHpParkInfo();
                lj.setId(rs.getString("id"));
                lj.setTotal(rs.getInt("total"));
                lj.setTotal_left(rs.getInt("total_left"));
                lj.setParkingNumber(rs.getInt("parkingNumber"));
                lj.setRecordTime(rs.getString("recordTime"));
                lj.setUpdateTime(rs.getString("updateTime"));
                lj.setParkId(rs.getString("parkId"));
                return lj;
            }
        }, new Object[]{parkId});
    }

    @Override
    public SysHpParkInfo getSysHpParkInfoByHour(String parkId, String s) {
        String sql = "select parkingNumber from hp_park_info where parkId=? and  recordTime like " + s;
        return getEntry(sql, new SysHpParkInfoRowMapper(), new Object[]{parkId});
    }

    @Override
    public SysHpRoadConditionInfo getlistSysHpRoadConditionInfo(String rid) {
        String sql = "select * from hp_road_condition_info  where roadId=?";

        return getEntry(sql, new SysHpRoadConditionInfoRowMapper(), new Object[]{rid});
    }

    @Override
    public void insertNewHpRoad(String coordinates, String inter_id, String inter_name, String rid, String rid_name) {
        String sql = "insert into hp_road(rid,coordinates,inter_id,inter_name,rid_name) values(?,?,?,?,?)";
        update(sql, new Object[]{rid, coordinates, inter_id, inter_name, rid_name});
    }

    @Override
    public List<SysHpRoad> getRoadIdsFromHpRoadCondition(Integer page, Integer pagesize) {
//        String sql="select * from hp_road";
//        return getEntryList(sql,new SysHpRoadRowMapper(),new Object[]{});
        StringBuffer sbBuffer = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        String sql = "select * from hp_road ";
        sbBuffer.append(" order by id asc");
        sbBuffer.append(" limit ? offset ?");
        params.add(pagesize);
        params.add((page - 1) * pagesize);
        return getEntryList(sql + sbBuffer.toString(), new SysHpRoadRowMapper(), params.toArray());
    }

    @Override
    public List<SysHpPublicVideo> getRoadIdsFromHpPublicVideo(int page, Integer pagesize) {
        StringBuffer sbBuffer = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        String sql = "select * from hp_public_video where video_lng is not null or video_lat is not null  ";
        sbBuffer.append(" order by video_number asc");
        // sbBuffer.append(" limit ? offset ?");
//        params.add(pagesize);
//        params.add((page - 1) * pagesize);
        return getEntryList(sql + sbBuffer.toString(), new SysHpPublicVideoRowMapper(), params.toArray());
    }

    @Override
    public List<SysHpTest> getListSysHpTest() {
        String sql = "select * from hp_test";
        return getEntryList(sql, new SysHpTestRowMapper(), new Object[]{});
    }

    @Override
    public Integer getXiaoFangYearCount() {
        String sql = "select count(1) from `hp_xiaofang_aj_20200714` where YEAR(lilint_time)=YEAR(NOW())";
        return queryInteger(sql, new Object[]{});
    }

    @Override
    public List<SysHpXiaoFangAjType> getHpXiaoFangAjType() {
        String sql = "select count(1) as count,aj_name_type as `name` from hp_xiaofang_aj_20200714 group by aj_name_type";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{});
    }

    @Override
    public int getXiaoFangAllCount() {
        String sql = "select count(1) from `hp_xiaofang_aj_20200714`";
        return queryInteger(sql, new Object[]{});
    }

    @Override
    public List<SysHpXiaoFangAjType> getXiaoFangAjTypeKeyString(String name) {
        String sql = "select dispose_object as `name`,count(1) as count from hp_xiaofang_aj_20200714 where aj_name_type=? group by dispose_object order by count desc";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{name});
    }

    @Override
    public Integer getXiaoFangKeyStringCountYear(String keystring, String start, String end) {
        String sql = "select count(1) from hp_xiaofang_aj_20200714 where dispose_object=? and lilint_time>=? and lilint_time<=?";
        return queryInteger(sql, new Object[]{keystring, start, end});
    }

    @Override
    public List<SysHpXiaoFangAjTypeHour> getListlistSysHpXiaoFangAjTypeHour(String keystring) {
        String sql = "SELECT HOUR( e.lilint_time ) AS hour,count(1) AS count FROM hp_xiaofang_aj_20200714 e WHERE e.dispose_object=? GROUP BY hour ( e.lilint_time )  ORDER BY hour ( e.lilint_time )";
        return getEntryList(sql, new SysHpXiaoFangAjTypeHourRowMapper(), new Object[]{keystring});
    }

    @Override
    public List<SysHpXiaoFangAjType> getListSysHpXiaoFangAjAreaData() {
        String sql = "select organization as `name`,count(1) as count from hp_xiaofang_aj_20200714  group by organization order by count desc";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{});
    }

    @Override
    public List<SysHpXiaoFangAjType> getListSysHpXiaoFangAjMonthData(String keystring) {
        String sql = "select date_format(lilint_time,'%m') as name ,count(1) as count  from hp_xiaofang_aj_20200714 where  dispose_object=?  group by name";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{keystring});
    }

    @Override
    public List<SysHpXiaoFangAjType> getHpXiaoFangAjTypeStation(String keystring) {
        String sql = "select count(1) as count,aj_name_type as `name` from hp_xiaofang_aj_20200714 where organization=? group by aj_name_type";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{keystring});
    }

    @Override
    public int getXiaoFangAllCountStation(String keystring) {
        String sql = "select count(1) from `hp_xiaofang_aj_20200714` where organization=?";
        return queryInteger(sql, new Object[]{keystring});
    }

    @Override
    public String getRoadIdByRoadName(String roadName) {
        String sql = "select roadId from hp_road_condition where roadName =? limit 0,1";
        return queryString(sql, new Object[]{roadName});
    }

    @Override
    public Integer getXiaoFangKeyStringCountYearType(String name, String start, String end) {

        String sql = "select count(1) from hp_xiaofang_aj_20200714 where aj_name_type=? and lilint_time>=? and lilint_time<=?";
        return queryInteger(sql, new Object[]{name, start, end});
    }

    /**
     * 获取近五年警情数据量
     * @return
     */
    @Override
    public List<YearCount>  getPoliceInfoCountGroupByYear(){
        String sql = "select COUNT(1) count, YEAR(lilint_time) year from hp_xiaofang_aj_20200714 where YEAR(lilint_time)>year(now()) -5 GROUP BY YEAR(lilint_time)";
        return getEntryList(sql, new RowMapper<YearCount>() {
            @Override
            public YearCount mapRow(ResultSet rs, int rowNum) throws SQLException {
                YearCount  yearCount= new YearCount();
                yearCount.setYear(rs.getString("year"));
                yearCount.setCount(rs.getString("count"));
                return yearCount;
            }
        }, new Object[]{});
    }


    /**
     * 获取当年消防情景类型的警情数
     * @return
     */
    @Override
    public List<PoliceInfoTypeCount>  getPoliceInfoCountGroupByType(){
        String sql = "select COUNT(1) count, aj_name_type type from hp_xiaofang_aj_20200714 where YEAR(lilint_time)>year(now()) -1 GROUP BY aj_name_type;";
        return getEntryList(sql, new RowMapper<PoliceInfoTypeCount>() {
            @Override
            public PoliceInfoTypeCount mapRow(ResultSet rs, int rowNum) throws SQLException {
                PoliceInfoTypeCount policeInfoTypeCount = new PoliceInfoTypeCount();
                policeInfoTypeCount.setType(rs.getString("type"));
                policeInfoTypeCount.setCount(rs.getString("count"));
                return policeInfoTypeCount;
            }
        }, new Object[]{});
    }



    @Override
    public List<SysHpXiaoFangAjType> getListSysHpXiaoFangAjTypeStation(String keystring) {
        String sql = "";
        if (keystring.equals("all")) {
            sql = "select count(1) as count,organization as `name` from hp_xiaofang_aj_20200714 group by organization";

        } else {
            sql=" select count(1) as count,organization as `name` from hp_xiaofang_aj_20200714 where lilint_time like ? group by organization";
            //"2015-01-01 00:00:00", "2015-12-31 24:59:59"
            /*if (keystring.equals("2015")) {
                sql = "select count(1) as count,organization as `name` from hp_xiaofang_aj_20200714 where lilint_time>='2015-01-01 00:00:00' and lilint_time<='2015-12-31 24:59:59' group by organization";
            }
            if (keystring.equals("2016")) {
                sql = "select count(1) as count,organization as `name` from hp_xiaofang_aj_20200714 where lilint_time>='2016-01-01 00:00:00' and lilint_time<='2016-12-31 24:59:59' group by organization";
            }
            if (keystring.equals("2017")) {
                sql = "select count(1) as count,organization as `name` from hp_xiaofang_aj_20200714 where lilint_time>='2017-01-01 00:00:00' and lilint_time<='2017-12-31 24:59:59' group by organization";
            }
            if (keystring.equals("2018")) {
                sql = "select count(1) as count,organization as `name` from hp_xiaofang_aj_20200714 where lilint_time>='2018-01-01 00:00:00' and lilint_time<='2018-12-31 24:59:59' group by organization";
            }
            if (keystring.equals("2019")) {
                sql = "select count(1) as count,organization as `name` from hp_xiaofang_aj_20200714 where lilint_time>='2019-01-01 00:00:00' and lilint_time<='2019-12-31 24:59:59' group by organization";
            }
            if (keystring.equals("2020")) {
                sql = "select count(1) as count,organization as `name` from hp_xiaofang_aj_20200714 where lilint_time>='2020-01-01 00:00:00' and lilint_time<='2020-12-31 24:59:59' group by organization";
            }*/
            return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{"%"+keystring+"%"});
        }
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{});

    }

    @Override
    public List<SysHpXiaoFangAj> getListSysHpXiaoFangAjKeyString(String keystring) {
        String sql = "select * from hp_xiaofang_aj_20200714 where dispose_object=? ";
        return getEntryList(sql, new SysHpXiaoFangAjRowMapper(), new Object[]{keystring});
    }












    @Override
    public List<SysHpPublicVideo> getRoadIdsFromHpPublicVideoByStreet(int page, Integer goodspagesize) {
        StringBuffer sbBuffer = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        String sql = "select * from hp_public_video where video_management like '%老西门%' ";
        sbBuffer.append(" order by video_number asc");
        // sbBuffer.append(" limit ? offset ?");
//        params.add(pagesize);
//        params.add((page - 1) * pagesize);
        return getEntryList(sql + sbBuffer.toString(), new SysHpPublicVideoRowMapper(), params.toArray());
    }

    @Override
    public List<SysMiniFireControl> getListSysMiniFireControl() {
        String sql = "select * from sys_miniature_firecontrol";
        return getEntryList(sql, new SysMiniFireControlRowMapper(), new Object[]{});
    }

    @Override
    public List<HpJcgsdssms> getHpJcgsdssms(){
        String sql = "select * from hp_jcgsdssms";
        return getEntryList(sql, new RowMapper<HpJcgsdssms>() {
            @Override
            public HpJcgsdssms mapRow(ResultSet resultSet, int i) throws SQLException {
                HpJcgsdssms hpJcgsdssms = new HpJcgsdssms();
                hpJcgsdssms.setId(resultSet.getInt("id"));
                hpJcgsdssms.setAddress(resultSet.getString("address"));
                return hpJcgsdssms;
            }
        }, new Object[]{});
    }

    @Override
    public void updateHpJcgsdssms(String lng, String lat, Integer id) {
        String sql = "update hp_jcgsdssms set lng=?,lat=? where id=?";
        update(sql, new Object[]{lng, lat, id});
    }

    @Override
    public void updateSysFireLngLatById(String lng, String lat, Integer id) {
        String sql = "update sys_miniature_firecontrol set lng=?,lat=? where id=?";
        update(sql, new Object[]{lng, lat, id});
    }

    @Override
    public SysHpScenicState getSysHpScenicStateBySpotCode(String spotCode) {
        String sql = "select * from hp_scenic_state where spotCode=?";
        return getEntry(sql, new SysHpScenicStateRowMapper(), new Object[]{spotCode});
    }

    @Override
    public List<ScenicAttendant> getListScenicAttendantByscenicpartment(String scenicpartment) {
        String sql = "select * from scenic_attendant where scenicpartment=? and date='2019-11-05'";
        return getEntryList(sql, new ScenicAttendantRowMapper(), new Object[]{scenicpartment});
    }

    @Override
    public List<SysRefinedCrowdSituation> getSysRefinedCrowdSituationBySpotCodePastHour(String spotCode, String pastHour) {
        String sql = "select * from sys_refined_crowdSituation where spotCode =? and createTime like ?";
        return getEntryList(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{spotCode, pastHour + "%"});
    }

    @Override
    public List<SpotRoadInfo> getListSpotRoadInfoBySpotCode(String spotCode) {
        StringBuffer sqlBuff = new StringBuffer();
        sqlBuff.append("SELECT	roadId,	spotCode,roadName,interName,speed,nostopSpeed FROM	(	SELECT	t2.roadId,t1.spotCode,t1.interName,	t1.roadName,t2.roadLevel,t2.nostopSpeed,t2.speed,t2.recordTime 	FROM	hp_road_condition t1 ");
        sqlBuff.append("INNER JOIN (	SELECT	roadId,roadLevel,nostopSpeed,speed,recordTime 	FROM	hp_road_condition_info 	WHERE	roadId IN ( SELECT roadId FROM hp_road_condition WHERE spotCode = ? )) t2 ON ");
        sqlBuff.append("t1.roadId = t2.roadId 	ORDER BY	roadName 	) tt1 ");

        String sql = sqlBuff.toString();
        return getEntryList(sql, new SpotRoadInfoRowMapper(), new Object[]{spotCode});
    }

    @Override
    public int getSpotRoadLevelByNameAndSpotCode(String spotCode, String roadName) {
        String sql = "select roadLevel from hp_road_condition_info where roadId in (select roadId from hp_road_condition where spotCode = ? and roadName = ? )limit 0,1";


        return queryInteger(sql, new Object[]{spotCode, roadName});
    }

    @Override
    public List<String> getListRoadNameBySpotCode(String spotCode) {
        String sql = "select DISTINCT roadName from hp_road_condition where spotCode =?";
        return queryStringArray(sql, new Object[]{spotCode});
    }

    @Override
    public List<Map<String, Object>> getListAliPersonCount(String time) {
//        String sql="select personcount  from alidata_personcount where type ='njdl' and create_time>=?  order by create_time asc";
//        String sql="select personcount  from alidata_personcount where type ='njdl' order by create_time asc";
        String sql = "select create_time ,personcount  from alidata_personcount where type ='njdl' and TO_DAYS(create_time) = TO_DAYS(NOW()) order by create_time  ";
        return queryListMap(sql, new Object[]{});
    }

    @Override
    public void insertNewLjEventData(String url, String deviceId, String deviceName, String area, String eventType, String eventTime) {
        String sql = "insert into sys_lj_eventdata(imageurl,deviceId,deviceName,area,eventType,eventTime,create_time) values(?,?,?,?,?,?,?)";
        update(sql, new Object[]{url, deviceId, deviceName, area, eventType, eventTime, new Date()});
    }

    @Override
    public String getListAliPersonCountByTime(String time) {
        String sql = "select personcount  from alidata_personcount where type ='njdl' and create_time<=? order by create_time desc limit 1  ";
        return queryString(sql, new Object[]{time});
    }

    @Override
    public List<String> getDZDPImageList() {
        String sql = "select imageurl from hp_dianzhaodianpai_info WHERE not ISNULL(imageurl)";
        return queryStringArray(sql, new Object[]{});
    }

    @Override
    public List<SysEventDataAll> findAllEventInfo(String eventTime) {
        String sql = "SELECT  e.id as guid,e.imageurl,e.deviceId ,e.deviceName,e.eventTime,e.create_time,v.* from sys_lj_eventdata as e  left join sys_lj_detection_video as v on e.deviceId=v.camera_id where e.eventTime>? order by e.eventTime desc ";
        return getEntryList(sql, new SysEventDataAllMapper(), new Object[]{eventTime});
    }

    @Override
    public List<SysEventDataAll> findEventInfo() {
        String sql = "SELECT  e.id as guid,e.imageurl,e.deviceId ,e.deviceName,e.eventTime,e.create_time,v.* from sys_lj_eventdata as e  left join sys_lj_detection_video as v on e.deviceId=v.camera_id  order by e.create_time desc limit 2 ";
        return getEntryList(sql, new SysEventDataAllMapper(), new Object[]{});

    }

    @Override
    public SysEventDataAll findEventInfoById(String ss) {
        Integer integer = Integer.valueOf(ss);
        String sql = "SELECT  e.id as guid,e.imageurl,e.deviceId ,e.deviceName,e.eventTime,e.create_time,v.* from sys_lj_eventdata as e  left join sys_lj_detection_video as v on e.deviceId=v.camera_id  where e.id=? ";
        return getEntry(sql, new SysEventDataAllMapper(), new Object[]{integer});
    }

    @Override
    public List<SysHpRoadConditionAll> getSpotRoadInfoData(String spotCode) {
        String sql = "SELECT roadId,roadName,interName,speed,nostopSpeed,tt1.roadLevel ,tt1.recordTime,tt1.updateTime FROM(SELECT t2.roadId,t1.spotCode,t1.interName,t1.roadName,t2.roadLevel,t2.nostopSpeed,t2.speed,t2.recordTime,t2.updateTime FROM hp_road_condition t1\n" +
                "\t\tINNER JOIN (SELECT roadId,roadLevel,nostopSpeed,speed,recordTime,updateTime FROM hp_road_condition_info WHERE roadId IN ( SELECT roadId FROM hp_road_condition WHERE spotCode =?) \n" +
                "\t\t) t2 ON t1.roadId = t2.roadId ORDER BY roadName ) tt1";
        return getEntryList(sql, new SysHpRoadConditionAllMapper(), new Object[]{spotCode});
    }

    @Override
    public List<SysHpSubway> getSubwayData(String spotCode) {
        String sql = "select spotCode,subwayName from hp_subway where spotCode=? GROUP BY subwayName";
        return getEntryList(sql, new SysHpSubwayRowMapper(), new Object[]{spotCode});
    }

    @Override
    public int getRZHBZ(String key) {
        String sql = "select sz from hp_syrk_street where jd=? and mc='人在户不在' order by cjrq desc limit 1";
        return queryInteger(sql, new Object[]{key});
    }

    @Override
    public int getHZRBZ(String key) {
        String sql = "select sz from hp_syrk_street where jd=? and mc='户在人不在' order by cjrq desc limit 1";
        return queryInteger(sql, new Object[]{key});
    }

    @Override
    public int getRZHZ(String key) {
        String sql = "select sz from hp_syrk_street where jd=? and mc='户在人在' order by cjrq desc limit 1";
        return queryInteger(sql, new Object[]{key});
    }

    @Override
    public List<SanitationInfo> getFxdkList() {
        String sql="select * from b_st_fxd_fxdk ";
        return getEntryList(sql, new SanitationInfoRowMapper(),new Object[]{});
    }

    @Override
    public Integer getXiaoFangDayCount() {
        String sql="select count(1) from `hp_xiaofang_aj_20200714` where DATE(lilint_time)=CURDATE() ";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Integer getXiaoFangMonthCount() {
        String sql="select count(1) from `hp_xiaofang_aj_20200714` where DATE_FORMAT(lilint_time,'%Y-%m')=DATE_FORMAT(now(),'%Y-%m')";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public List<HpMenu> getPlanType(String type) {
        String sql="select title,level from hp_yuan_dictionary where type=? and parent_id='root'";
        return getEntryList(sql, new RowMapper<HpMenu>() {
            @Override
            public HpMenu mapRow(ResultSet resultSet, int i) throws SQLException {
                HpMenu hpMenu=new HpMenu();
                hpMenu.setTitle(resultSet.getString("title"));
                hpMenu.setLevel(resultSet.getString("level"));
                return hpMenu;
            }
        }, new Object[]{type});
    }

    @Override
    public String getTitle(String title) {
        String sql="select name from hp_yuan_dictionary where title=?";
        return queryString(sql,new Object[]{title});
    }

    @Override
    public List<HpMenu> getContingencyPlan(String type, String title,String level) {
        String sql="select d.*,c.detail from hp_yuan_dictionary d left join hp_yuan_code c on c.yuan_code=d.id where type =? and level=? order by d.sorts ";
        return getEntryList(sql, new RowMapper<HpMenu>() {
            @Override
            public HpMenu mapRow(ResultSet resultSet, int i) throws SQLException {
                HpMenu hpMenu=new HpMenu();
                hpMenu.setId(resultSet.getString("id"))
                        .setTitle(resultSet.getString("title"))
                        .setParent_id(resultSet.getString("parent_id"))
                        .setType(resultSet.getString("type"))
                        .setDetail(resultSet.getString("detail"))
                        .setSorts(resultSet.getInt("sorts"));
                return hpMenu;
            }
        },new Object[]{type,level});
    }

    @Override
    public List<Map<String, Object>> getWtAliPersonCount(String todayZero) {
        String sql = "select create_time ,personcount  from alidata_personcount where type ='wt' and TO_DAYS(create_time) = TO_DAYS(NOW()) order by create_time  ";
        return queryListMap(sql, new Object[]{});
    }

    @Override
    public List<HpBMedical> getHospitalShelter() {
        String sql="select yljgmc,jgdz,POINT_X,POINT_Y from b_medical ";
        return getEntryList(sql, new RowMapper<HpBMedical>() {
            @Override
            public HpBMedical mapRow(ResultSet resultSet, int i) throws SQLException {
                HpBMedical hp=new HpBMedical();
                hp.setPOINT_X(resultSet.getString("POINT_X"));
                hp.setPOINT_Y(resultSet.getString("POINT_Y"));
                hp.setYljgmc(resultSet.getString("yljgmc"));
                hp.setJgdz(resultSet.getString("jgdz"));
                return hp;
            }
        }, new Object[]{});
    }

    @Override
    public List<YearSpotAnalysis> getEveryYearSpotAnalysis(String year) {
            String sql = "select SUBSTR(times from 1 for 7) times,SUM(total_number) total_number from nanjing_total_number where  SUBSTR(times from 1 for 4) like ? GROUP BY SUBSTR(times from 1 for 7)";
            return getEntryList(sql, new RowMapper<YearSpotAnalysis>() {
                @Override
                public YearSpotAnalysis mapRow(ResultSet rs, int i) throws SQLException {
                    YearSpotAnalysis yearSpotAnalysis=new YearSpotAnalysis();
                    yearSpotAnalysis.setSpotid("NANJINGLU");
                    yearSpotAnalysis.setTimes(rs.getString("times"));
                    yearSpotAnalysis.setMonth_count(rs.getString("total_number"));
                    return yearSpotAnalysis;
                }
            }, new Object[]{ "%" + year + "%"});
    }

    @Override
    public Integer getNjdlHistoryTotalNumber(String day) {
        String sql="select total_number from nanjing_total_number where times=?";
        return queryInteger(sql,new Object[]{day});
    }

    @Override
    public String getNanJingHuanBiNow() {
        String sql="select ratio from nanjing_total_number_hb order by times desc limit 1";
        return queryString(sql,new Object[]{});
    }

    @Override
    public NanjingTotalNumberHb getNanJingLastMonthSameDayInfo(String dateStr){
        String sql = "select spotName, times, total_number, last_month_spotName, last_month_day, last_month_total_number, ratio from nanjing_total_number_hb where times = ? limit 1;";
        NanjingTotalNumberHb nanjingTotalNumberHb = null;
        try{
            nanjingTotalNumberHb =  getEntry(sql, new RowMapper<NanjingTotalNumberHb>() {
                @Override
                public NanjingTotalNumberHb mapRow(ResultSet resultSet, int i) throws SQLException {
                    NanjingTotalNumberHb nanjingTotalNumberHb = new NanjingTotalNumberHb();
                    nanjingTotalNumberHb.setSpotName(resultSet.getString("spotName"));
                    nanjingTotalNumberHb.setTimes(resultSet.getString("times"));
                    nanjingTotalNumberHb.setTotalNumber(resultSet.getInt("total_number"));
                    nanjingTotalNumberHb.setLastMonthSpotName(resultSet.getString("last_month_spotName"));
                    nanjingTotalNumberHb.setLastMonthDay(resultSet.getString("last_month_day"));
                    nanjingTotalNumberHb.setLastMonthTotalNumber(resultSet.getInt("last_month_total_number"));
                    nanjingTotalNumberHb.setRatio(resultSet.getString("ratio"));
                    return nanjingTotalNumberHb;
                }
            }, new Object[]{dateStr});
        }catch (Exception e){
            e.printStackTrace();
        }
        return  nanjingTotalNumberHb;
    }



    @Override
    public String getNanJingHuanBi(String day) {
        String sql="select ratio from nanjing_total_number_hb where times=?";
        return queryString(sql,new Object[]{day});
    }

    @Override
    public List<SysHpPark> getParkInfo() {
        String sql="select hp.parkAddress,hp.parkId,hp.parkName,hp.lng,hp.lat,hf.parkingNumber,hf.total,hf.total_left from hp_park hp left  join hp_park_info hf on hp.parkId=hf.parkId  ";
        return getEntryList(sql, new RowMapper<SysHpPark>() {
            @Override
            public SysHpPark mapRow(ResultSet resultSet, int i) throws SQLException {
                SysHpPark hp=new SysHpPark();
                hp.setParkAddress(resultSet.getString("parkAddress"));
                hp.setParkId(resultSet.getString("parkId"));
                hp.setParkName(resultSet.getString("parkName"));
                hp.setLng(resultSet.getString("lng"));
                hp.setLat(resultSet.getString("lat"));
                hp.setParkingNumber(resultSet.getInt("parkingNumber"));
                hp.setTotal(resultSet.getInt("total"));
                hp.setTotal_left(resultSet.getInt("total_left"));
                return hp;
            }
        },new Object[]{});
    }

    @Override
    public void insert(String valueOf, String valueOf1, String parkId) {
        String sql="update  hp_park set lng=?,lat=? where parkId=?";
        update(sql,new Object[]{valueOf,valueOf1,parkId});
    }

    @Override
    public List<SysHpPark> getList() {
        String sql="select * from hp_park";
        return getEntryList(sql,new SysHpParkRowMapper(),new Object[]{});
    }

    @Override
    public SysRefinedCrowdSituation getTotalAllNumber(String day, String day1, String spotName) {
        if (day.equals( DateUtils.getDate())){
            String sql = "select *  from sys_refined_crowdSituation where spotCode=? and createTime >=? and createTime <=? order by totalNumber desc limit 0,1";
            return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{spotName, day + " 00:00:00", day1 + " 23:59:59"});

        }
        else {
            String sql = "select *  from sys_refined_crowdSituation_history where spotCode=? and   createTime >=? and createTime <=? order by totalNumber desc limit 0,1";
            return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{ spotName, day + " 00:00:00", day1 + " 23:59:59"});
        }
    }

    @Override
    public SysRefinedCrowdSituation getMaxAllNumber(String day, String day1, String spotName) {
        if (day.equals( DateUtils.getDate())){
            String sql = "select *  from sys_refined_crowdSituation where spotCode=? and   createTime >=? and createTime <=? order by realtimeNumber desc limit 0,1";
            return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{spotName,day + " 00:00:00", day1 + " 23:59:59"});

        }else {
            String sql = "select *  from sys_refined_crowdSituation_history where spotCode=? and  createTime >=? and createTime <=? order by realtimeNumber desc limit 0,1";
            return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{spotName,day + " 00:00:00", day1 + " 23:59:59"});
        }
    }

    @Override
    public List<SysRefinedCrowdSituation> getRealTimeAllCount(String spotName, String day) {
        if (day.equals(DateUtils.getDate())) {
            String sql ="select * from sys_refined_crowdSituation where  createTime like ?;";
            return  getEntryList(sql, new RowMapper<SysRefinedCrowdSituation>() {
                @Override
                public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                    SysRefinedCrowdSituation sc = new SysRefinedCrowdSituation();
                    sc.setCreateTime(resultSet.getString("createTime"));
                    sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                    sc.setSpotName(resultSet.getString("spotName"));
                    return sc;
                }
            },new Object[]{day+"%"});
        }else {
            String sql ="select * from sys_refined_crowdSituation_history where  createTime  like ?;";
            return  getEntryList(sql, new RowMapper<SysRefinedCrowdSituation>() {
                @Override
                public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                    SysRefinedCrowdSituation sc = new SysRefinedCrowdSituation();
                    sc.setCreateTime(resultSet.getString("createTime"));
                    sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                    sc.setSpotName(resultSet.getString("spotName"));
                    return sc;
                }
            },new Object[]{day+"%"});}
    }

    @Override
    public List<SysRefinedCrowdSituation> getHistorySpotDataAllCount(String spotName, String day) {
        if (day.equals(DateUtils.getDate())) {
            String sql ="SELECT spotName,createTime,realTimeNumber FROM (SELECT spotName,realTimeNumber,createTime,row_number () over ( PARTITION BY substr( createTime, 1, 13 ), spotName ORDER BY createTime ASC ) rn FROM sys_refined_crowdSituation WHERE  createTime LIKE ? ) tmp WHERE " +
                    "rn = 1";
            return  getEntryList(sql, new RowMapper<SysRefinedCrowdSituation>() {
                @Override
                public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                    SysRefinedCrowdSituation sc = new SysRefinedCrowdSituation();
                    sc.setCreateTime(resultSet.getString("createTime"));
                    sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                    sc.setSpotName(resultSet.getString("spotName"));
                    return sc;
                }
            },new Object[]{day+"%"});}else {
            String sql ="SELECT spotName,createTime,realTimeNumber FROM (SELECT spotName,realTimeNumber,createTime,row_number () over ( PARTITION BY substr( createTime, 1, 13 ), spotName ORDER BY createTime ASC ) rn FROM sys_refined_crowdSituation_history WHERE createTime LIKE ? ) tmp WHERE " +
                    "rn = 1";
            return  getEntryList(sql, new RowMapper<SysRefinedCrowdSituation>() {
                @Override
                public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                    SysRefinedCrowdSituation sc = new SysRefinedCrowdSituation();
                    sc.setCreateTime(resultSet.getString("createTime"));
                    sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                    sc.setSpotName(resultSet.getString("spotName"));
                    return sc;
                }
            },new Object[]{day+"%"});
        }
    }

    @Override
    public SysRefinedCrowdSituation getTotalNumber(String s1, String s, String spotName) {
        if (s1.equals( DateUtils.getDate())){
            String sql = "select *  from sys_refined_crowdSituation where spotName=?  and createTime >=? and createTime <=? order by totalNumber desc limit 0,1";
            return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{spotName, s1 + " 00:00:00", s + " 23:59:59"});

        }
        else {
            String sql = "select *  from sys_refined_crowdSituation_history where spotName=?  and createTime >=? and createTime <=? order by totalNumber desc limit 0,1";
            return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{spotName, s1 + " 00:00:00", s + " 23:59:59"});
        }
    }

    @Override
    public SysRefinedCrowdSituation getMaxNumber(String s1, String s, String spotName) {
        if (s1.equals( DateUtils.getDate())){
            String sql = "select *  from sys_refined_crowdSituation where spotName=?  and createTime >=? and createTime <=? order by realtimeNumber desc limit 0,1";
            return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{spotName, s1 + " 00:00:00", s + " 23:59:59"});

        }else {
            String sql = "select *  from sys_refined_crowdSituation_history where spotName=?  and createTime >=? and createTime <=? order by realtimeNumber desc limit 0,1";
            return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{spotName, s1 + " 00:00:00", s + " 23:59:59"});
        } }

    @Override
    public List<SysRefinedCrowdSituation> getRealTimeCount(String spotName, String day) {
        if (day.equals(DateUtils.getDate())) {
            String sql ="select * from sys_refined_crowdSituation where spotName=? and createTime like ?;";
            return  getEntryList(sql, new RowMapper<SysRefinedCrowdSituation>() {
                @Override
                public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                    SysRefinedCrowdSituation sc = new SysRefinedCrowdSituation();
                    sc.setCreateTime(resultSet.getString("createTime"));
                    sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                    sc.setSpotName(resultSet.getString("spotName"));
                    return sc;
                }
            },new Object[]{spotName,day+"%"});
        }else {
        String sql ="select * from sys_refined_crowdSituation_history where spotName=? and createTime  like ?;";
        return  getEntryList(sql, new RowMapper<SysRefinedCrowdSituation>() {
            @Override
            public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                SysRefinedCrowdSituation sc = new SysRefinedCrowdSituation();
                sc.setCreateTime(resultSet.getString("createTime"));
                sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                sc.setSpotName(resultSet.getString("spotName"));
                return sc;
            }
        },new Object[]{spotName,day+"%"});}
    }

    @Override
    public List<SysRefinedCrowdSituation> getHistorySpotDataCount(String spotName, String day) {
        if (day.equals(DateUtils.getDate())) {
        String sql ="SELECT spotName,createTime,realTimeNumber FROM (SELECT spotName,realTimeNumber,createTime,row_number () over ( PARTITION BY substr( createTime, 1, 13 ), spotName ORDER BY createTime ASC ) rn FROM sys_refined_crowdSituation WHERE spotName = ? AND createTime LIKE ? ) tmp WHERE " +
                "rn = 1";
        return  getEntryList(sql, new RowMapper<SysRefinedCrowdSituation>() {
            @Override
            public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                SysRefinedCrowdSituation sc = new SysRefinedCrowdSituation();
                sc.setCreateTime(resultSet.getString("createTime"));
                sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                sc.setSpotName(resultSet.getString("spotName"));
                return sc;
            }
        },new Object[]{spotName,day+"%"});}else {
            String sql ="SELECT spotName,createTime,realTimeNumber FROM (SELECT spotName,realTimeNumber,createTime,row_number () over ( PARTITION BY substr( createTime, 1, 13 ), spotName ORDER BY createTime ASC ) rn FROM sys_refined_crowdSituation_history WHERE spotName = ? AND createTime LIKE ? ) tmp WHERE " +
                    "rn = 1";
            return  getEntryList(sql, new RowMapper<SysRefinedCrowdSituation>() {
                @Override
                public SysRefinedCrowdSituation mapRow(ResultSet resultSet, int i) throws SQLException {
                    SysRefinedCrowdSituation sc = new SysRefinedCrowdSituation();
                    sc.setCreateTime(resultSet.getString("createTime"));
                    sc.setRealtimeNumber(resultSet.getInt("realtimeNumber"));
                    sc.setSpotName(resultSet.getString("spotName"));
                    return sc;
                }
            },new Object[]{spotName,day+"%"});
        }
    }

    @Override
    public Integer getPopuHourseByYear() {
        String sql="select SUBSTR(cjrq,1,4) month from hp_syrk_house_statistics order by cjrq desc limit 1";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public Integer getPopuHourseByMonth() {
        String sql="select SUBSTR(cjrq,5,6) month from hp_syrk_house_statistics order by cjrq desc limit 1";
        return queryInteger(sql,new Object[]{});
    }

    @Override
    public List<String> getMonthGenderByAge() {
        String sql="select times month from hp_syrk_gender group by times  order by times desc limit 5";
        return queryStringArray(sql,new Object[]{});
    }

    @Override
    public List<HpPersonHouseStatistics> getFloatingPopulationByStreet(String key) {
        String sql="select sz,jd from hp_syrk_street where mc=? order by cjrq,jd desc limit 10  ";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h=new HpPersonHouseStatistics();
                h.setSz(resultSet.getInt("sz"));
                h.setJd(resultSet.getString("jd"));
                return h;
            }
        }, new Object[]{key});
    }

    @Override
    public List<HpSyrkAgeDistribute> getFloatingPopulationTrend(String key) {
        String sql="select sz,cjrq  from hp_syrk_hp_popu where mc=? order by cjrq asc ";

        return getEntryList(sql, new RowMapper<HpSyrkAgeDistribute>() {
            @Override
            public HpSyrkAgeDistribute mapRow(ResultSet resultSet, int i) throws SQLException {
                HpSyrkAgeDistribute h =new HpSyrkAgeDistribute();
                h.setCjrq(resultSet.getString("cjrq"));
                h.setSz(resultSet.getString("sz"));
                return h;
            }
        },new Object[]{key});
    }

    @Override
    public String getNowMaxCountByStreet(String key) {
        String sql="SELECT jd   from (select jd,mc,sz,cjrq from hp_syrk_street where mc=? GROUP BY  jd,mc,sz,cjrq order by cjrq desc limit 10) s order by sz desc  limit 1";
        return queryString(sql,new Object[]{key});
    }

    @Override
    public Integer getLaiHuHb(String key) {
        String s = lastMonth();
        String sql = "select sz from hp_syrk_hp_popu where  mc=? and cjrq=?";
        return queryInteger(sql, new Object[]{key,s});
    }

    @Override
    public Integer getLaiHuTb(String key) {
        String sql0 = "select YEAR(NOW())";
        String sql01 = "select SUBSTR(cjrq from 5 for 6) from hp_syrk_hp_popu    ORDER BY cjrq desc limit 1 ";
        int integer0 = queryInteger(sql0, new Object[]{});
        String integer01 = queryString(sql01, new Object[]{});
        int a = integer0 - 1;
//        String date = String.valueOf(a) + String.valueOf(integer01);
        Integer j=null;
        Integer k=null;
        String date1=null;
        if (Integer.valueOf(integer01)-1==0) {
            j=12;
            k=integer0-1;
             date1=String.valueOf(k)+String.valueOf(j);
        }else {
            date1=String.valueOf(a)+String.valueOf(integer01);
        }
        String sql = "select sz from hp_syrk_hp_popu where  mc=? and cjrq=?";
        return   queryInteger(sql, new Object[]{key,date1});

    }

    @Override
    public Integer getLaiHu(String key) {
        String sql = "select sz from hp_syrk_hp_popu where mc=? order by cjrq desc limit 1";
        return queryInteger(sql, new Object[]{key});
    }

    @Override
    public List<HpSyrkAgeDistribute> getSevenToFourteen() {
        String sql = "select age_distribute,sz,cjrq from hp_syrk_age_distribute where age_distribute='7-14岁' order by cjrq desc  limit 5";
        return getEntryList(sql, new HpSyrkAgeDistributeRowMapper(), new Object[]{});
    }

    @Override
    public List<HpSyrkAgeDistribute> getOneToSix() {
        String sql = "select age_distribute,sz,cjrq from hp_syrk_age_distribute where age_distribute='0-6岁儿童' order by cjrq desc  limit 5";
        return getEntryList(sql, new HpSyrkAgeDistributeRowMapper(), new Object[]{});
    }

    @Override
    public List<HpSyrkAgeDistribute> getSixtySixUp() {
        String sql = "select age_distribute,sz,cjrq from hp_syrk_age_distribute where age_distribute='66以上' order by cjrq desc  limit 5";
        return getEntryList(sql, new HpSyrkAgeDistributeRowMapper(), new Object[]{});
    }

    @Override
    public List<HpSyrkAgeDistribute> getSixtyUp() {
        String sql = "select age_distribute,sz,cjrq from hp_syrk_age_distribute where age_distribute='60岁以上' order by cjrq desc  limit 5";
        return getEntryList(sql, new HpSyrkAgeDistributeRowMapper(), new Object[]{});
    }

    @Override
    public List<HpPersonHouseStatistics> getCountByType() {
        String sql = "SELECT sum(s.sz) count,jd from (select jd,mc,sz,cjrq from hp_syrk_street where (mc='户在人不在' or mc='人在户不在' or mc='户在人在' or mc='去向不明' )  group by jd,mc,sz,cjrq order by cjrq desc limit 40) s group by jd";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setCount(resultSet.getInt("count"));
                h.setJd(resultSet.getString("jd"));
                return h;
            }
        }, new Object[]{});
    }

    @Override
    public List<HpSyrkGender> getGenderByAgeWoman(String year) {
        String sql = "select * from hp_syrk_gender where ly='户籍人口(女)' and times=?";
        return getEntryList(sql, new RowMapper<HpSyrkGender>() {
            @Override
            public HpSyrkGender mapRow(ResultSet resultSet, int i) throws SQLException {
                HpSyrkGender hs = new HpSyrkGender();
                hs.setAge_cnts(resultSet.getString("age_cnts"));
                hs.setId(resultSet.getInt("id"));
                hs.setLy(resultSet.getString("ly"));
                hs.setSz(resultSet.getInt("sz"));
                hs.setTimes(resultSet.getString("times"));
                return hs;
            }
        }, new Object[]{year});
    }

    @Override
    public List<HpSyrkGender> getGenderByAgeMan(String year) {
        String sql = "select * from hp_syrk_gender where ly='户籍人口(男) ' and times=?";
        return getEntryList(sql, new RowMapper<HpSyrkGender>() {
            @Override
            public HpSyrkGender mapRow(ResultSet resultSet, int i) throws SQLException {
                HpSyrkGender hs = new HpSyrkGender();
                hs.setAge_cnts(resultSet.getString("age_cnts"));
                hs.setId(resultSet.getInt("id"));
                hs.setLy(resultSet.getString("ly"));
                hs.setSz(resultSet.getInt("sz"));
                hs.setTimes(resultSet.getString("times"));
                return hs;
            }
        }, new Object[]{year});
    }

    @Override
    public List<HpPersonHouseStatistics> getNdStreetByType(String key) {
        String sql = "select SUBSTR(cjrq from 1 for 4) from hp_syrk_street order by cjrq desc limit 1";
        String s = queryString(sql, new Object[]{});
        String sql1 = "select cjrq,sz,mc from hp_syrk_street where jd=? and (mc='去向不明' or mc='人在户不在' or mc='户在人不在' or mc='户在人在') and cjrq like ? ";
        return getEntryList(sql1, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setCjrq(resultSet.getString("cjrq"));
                h.setSz(resultSet.getInt("sz"));
                h.setMc(resultSet.getString("mc"));
                return h;
            }
        }, new Object[]{key,"%" + s + "%"});

    }

    @Override
    public int getQXBM(String key) {
        String sql = "select sz from hp_syrk_street where jd=? and mc='去向不明' order by cjrq desc limit 1";
        return queryInteger(sql, new Object[]{key});
    }

    @Override
    public List<HpPersonHouseStatistics> geteveryYearTrendR() {
        String sql = "select mc,SUBSTR(cjrq FROM 1 FOR 4) date,AVG( sz) sz from hp_syrk_hp_popu where mc='户在人不在' GROUP BY SUBSTR(cjrq FROM 1 FOR 4),mc order by date";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setMc(resultSet.getString("mc"));
                h.setMc(resultSet.getString("date"));
                h.setSz(resultSet.getInt("sz"));
                return h;
            }
        }, new Object[]{});
    }

    @Override
    public List<HpPersonHouseStatistics> getPopuDistributionByStreet() {

        String sql = "select jd,mc,sz,cjrq from hp_syrk_street where (mc='户在人不在' or mc='人在户不在' or mc='户在人在' or mc='去向不明' )  group by jd,mc,sz,cjrq order by cjrq desc limit 40";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setJd(resultSet.getString("jd"));
                h.setMc(resultSet.getString("mc"));
                h.setCjrq(resultSet.getString("cjrq"));
                h.setSz(resultSet.getInt("sz"));
                return h;
            }
        }, new Object[]{});
    }

    @Override
    public Integer getNowCount() {
        String sql = "SELECT sum(num.sz) from (select sz,jd from hp_syrk_street where   mc='户在人在' order by cjrq desc limit 10 ) num";
        return queryInteger(sql, new Object[]{});
    }

    @Override
    public List<HpPersonHouseStatistics> getNowCountByStreet() {
        String sql = "select * from (select sz,jd from hp_syrk_street where mc='户在人在' order by cjrq desc limit 10) s order by jd ";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setSz(resultSet.getInt("sz"));
                h.setJd(resultSet.getString("jd"));
                return h;
            }
        }, new Object[]{});
    }

    @Override
    public Integer getLastCount() {
        String s = lastMonth();
        String sql = "SELECT sum(num.sz) from (select sz,jd from hp_syrk_street where cjrq= ? and mc='户在人在' ) num";
        return queryInteger(sql, new Object[]{s});
    }

    public String lastMonth() {
        String sql0 = "select SUBSTR(cjrq from 1 for 4) from hp_syrk_hp_popu   ORDER BY cjrq desc limit 1";
        String sql01 = "select SUBSTR(cjrq from 5 for 6) from hp_syrk_hp_popu   ORDER BY cjrq desc limit 1 ";
        int integer01 = queryInteger(sql0, new Object[]{});
        String integer0 = queryString(sql01, new Object[]{});
//        String s = String.valueOf(integer0) + String.valueOf(integer01 - 1);
        Integer j=null;
        Integer k=null;
        String date1=null;
        if (Integer.valueOf(integer0)-1==0) {
            j=12;
            k=Integer.valueOf( integer0)-1;
             date1=String.valueOf(k)+String.valueOf(j);
        }else{
            if (Integer.valueOf(integer0)-1<10) {
                date1 = String.valueOf(integer01) +"0" +String.valueOf(Integer.valueOf(integer0)-1);
            }else {
                date1 = String.valueOf(integer01) + String.valueOf(Integer.valueOf(integer0)-1);
            }

        }

        return date1;
    }

    @Override
    public List<HpPersonHouseStatistics> getCountByStreet() {
        String i = lastMonth();
        String sql = "select * from (select sz,jd from hp_syrk_street where cjrq= ? and mc='户在人在') s order by jd ";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setJd(resultSet.getString("jd"));
                h.setSz(resultSet.getInt("sz"));
                return h;
            }
        }, new Object[]{i});
    }

    @Override
    public List<HpPersonHouseStatistics> geteveryYearTrend() {
        String sql = "select mc,SUBSTR(cjrq FROM 1 FOR 4) date,AVG( sz) sz from hp_syrk_hp_popu where mc='人在户不在' GROUP BY SUBSTR(cjrq FROM 1 FOR 4),mc order by date";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setMc(resultSet.getString("mc"));
                h.setMc(resultSet.getString("date"));
                h.setSz(resultSet.getInt("sz"));
                return h;
            }
        }, new Object[]{});
    }

    @Override
    public List<HpPersonHouseStatistics> getPopuHourseNoFen(String year) {
        String sql = "select jd,cjrq,sz from hp_syrk_house_statistics where mc='无人户分离' and cjrq=? GROUP BY jd,cjrq,sz order by jd";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setCjrq(resultSet.getString("cjrq"));
                h.setJd(resultSet.getString("jd"));
                h.setSz(resultSet.getInt("sz"));
                return h;
            }
        }, new Object[]{year});
    }

    @Override
    public List<HpPersonHouseStatistics> getPopuHourse(String year) {
        String sql = "select jd,cjrq,sz from hp_syrk_house_statistics where mc='有人户分离' and cjrq=? GROUP BY jd,cjrq,sz order by jd";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setCjrq(resultSet.getString("cjrq"));
                h.setJd(resultSet.getString("jd"));
                h.setSz(resultSet.getInt("sz"));
                return h;
            }
        }, new Object[]{year});
    }

    @Override
    public List<HpPersonHouseStatistics> getEveryYearHjRk() {
        String sql = "select sz,cjrq from hp_syrk_hp_popu where mc='户籍人口' ORDER BY cjrq asc limit 6,1000;";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics h = new HpPersonHouseStatistics();
                h.setCjrq(resultSet.getString("cjrq"));
                h.setSz(resultSet.getInt("sz"));
                return h;
            }
        }, new Object[]{});
    }

    @Override
    public List<HpPersonHouseStatistics> getEveryYear() {
        String sql = "select sz,cjrq from hp_syrk_hp_popu where mc='实有人口' ORDER BY cjrq asc limit 6,1000;";
        return getEntryList(sql, new RowMapper<HpPersonHouseStatistics>() {
            @Override
            public HpPersonHouseStatistics mapRow(ResultSet resultSet, int i) throws SQLException {
                HpPersonHouseStatistics hpPersonHouseStatistics = new HpPersonHouseStatistics();
                hpPersonHouseStatistics.setSz(resultSet.getInt("sz"));
                hpPersonHouseStatistics.setCjrq(resultSet.getString("cjrq"));
                return hpPersonHouseStatistics;
            }
        }, new Object[]{});
    }

    @Override
    public HpPersonHouseStatistics getHuJiCount() {
        //户籍人口
        String sql = "select sz from hp_syrk_hp_popu  where mc='户籍人口' ORDER BY cjrq desc limit 1 ";
        //户在人在
        String sql1 = "select sz from hp_syrk_hp_popu where  mc='户在人在 ' ORDER BY cjrq desc limit 1 ";
        //户在人不在
        String sql2 = "select sz from hp_syrk_hp_popu where  mc='户在人不在 ' ORDER BY cjrq desc limit 1 ";
        //去向不明
        String sql3 = "select sum(sz) from hp_syrk_hp_popu where (mc='空挂户' or mc='去向不明') GROUP BY cjrq ORDER BY cjrq desc limit 1";
        Integer integer = queryInteger(sql, new Object[]{});
        Integer integer1 = queryInteger(sql1, new Object[]{});
        Integer integer2 = queryInteger(sql2, new Object[]{});
        Integer integer3 = queryInteger(sql3, new Object[]{});
        HpPersonHouseStatistics hp = new HpPersonHouseStatistics();
        hp.setSz(integer);
        hp.setHzrz(integer1);
        hp.setHzrbz(integer2);
        hp.setQxbm(integer3);
        return hp;
    }

    @Override
    public HpPersonHouseStatistics getPopuDistribution() {
        String sql0 = "select SUBSTR(cjrq from 1 for 4) from hp_syrk_hp_popu    ORDER BY cjrq desc limit 1";
        String sql01 = "select SUBSTR(cjrq from 5 for 6) from hp_syrk_hp_popu    ORDER BY cjrq desc limit 1 ";
        int integer0 = queryInteger(sql0, new Object[]{});
        String integer01 = queryString(sql01, new Object[]{});
        int a = integer0 - 1;
        String date1=null;
        String date=null;
        Integer j=null;
        Integer k=null;
        date = String.valueOf(a) + String.valueOf(integer01);
        if (Integer.valueOf(integer01)-1==0) {
             j=12;
            k=integer0-1;
             date1=String.valueOf(k)+String.valueOf(j);
        }else{
            if (Integer.valueOf(integer01)-1<10) {
                date1 = String.valueOf(a) +"0"+ String.valueOf(Integer.valueOf(integer01)-1);
            }else {

             date1 = String.valueOf(a) + String.valueOf(integer01);}
        }


        //当月
        String sql = "select sz from hp_syrk_hp_popu where  mc='实有人口' ORDER BY cjrq desc limit 1 ";
        //去年同月
        String sql1 = "select sz from hp_syrk_hp_popu where cjrq=?  and mc='实有人口' ";
        //上月
        String sql2 = "select sz from hp_syrk_hp_popu where cjrq= ? and mc='实有人口'";
        int now = queryInteger(sql, new Object[]{});
        int last = queryInteger(sql2, new Object[]{date1});
        int lastNow = queryInteger(sql1, new Object[]{date});
        int i = now - last;
        double i1 = (Double.valueOf(now) / Double.valueOf(lastNow)) - 1;
        double i2 = (Double.valueOf(now) / Double.valueOf(last)) - 1;
        HpPersonHouseStatistics hp = new HpPersonHouseStatistics();
        hp.setCount(now);
        hp.setChange(i);
        hp.setTb(i1);
        hp.setHb(i2);
        return hp;
    }

    @Override
    public Integer getNjdlTotalNumber() {
        String sql = "SELECT sum( max_cnts ) FROM(SELECT CONCAT(DATE_FORMAT( createTime, '%Y-%m-%d %H ' ),CASE WHEN MINUTE ( createTime )<= 20 THEN '上'  WHEN MINUTE ( createTime ) >= 20 AND MINUTE ( createTime ) <= 40 THEN '中' ELSE '下' END ) AS times, max( realtimeNumber ) AS max_cnts FROM sys_refined_crowdSituation WHERE spotName = '南京路' AND createTime >= DATE_FORMAT(CURRENT_DATE() ,'%Y-%m-%d %H:%i:%s') \n" +
                "   AND createTime <= now() GROUP BY CONCAT(DATE_FORMAT( createTime, '%Y-%m-%d %H ' ),CASE WHEN MINUTE ( createTime )<= 20 THEN '上' WHEN MINUTE ( createTime ) >= 20 AND MINUTE ( createTime ) <=40 THEN'中' ELSE '下' " +
                "     END )) tmp GROUP BY substr( times, 1, 10 )";
        return queryInteger(sql, new Object[]{});
    }

    @Override
    public Integer getWaiTanAliTotalNumber() {
        String sql = "SELECT sum( max_cnts ) FROM(SELECT CONCAT(DATE_FORMAT( createTime, '%Y-%m-%d %H ' ),CASE WHEN MINUTE ( createTime )<= 20 THEN '上'  WHEN MINUTE ( createTime ) >= 20 AND MINUTE ( createTime ) <= 40 THEN '中' ELSE '下' END ) AS times, max( realtimeNumber ) AS max_cnts FROM sys_refined_crowdSituation WHERE spotCode = 'SHHPQ_WAITAN_Ali' AND createTime >= DATE_FORMAT(CURRENT_DATE() ,'%Y-%m-%d %H:%i:%s') \n" +
                "   AND createTime <= now() GROUP BY CONCAT(DATE_FORMAT( createTime, '%Y-%m-%d %H ' ),CASE WHEN MINUTE ( createTime )<= 20 THEN '上' WHEN MINUTE ( createTime ) >= 20 AND MINUTE ( createTime ) <=40 THEN'中' ELSE '下' " +
                "     END )) tmp GROUP BY substr( times, 1, 10 )";
        return queryInteger(sql, new Object[]{});
    }


    @Override
    public int getXiaoFangAllCountByYear(String year) {
        String sql = "select count(1) from `hp_xiaofang_aj_20200714` where  SUBSTR(lilint_time from 1 for 4) like ?";
        return queryInteger(sql, new Object[]{"%" + year + "%"});
    }

    @Override
    public List<SysHpXiaoFangAjType> getXiaofangTypeDataByYear(String year) {
        String sql = "select count(1) as count,aj_name_type as `name` from hp_xiaofang_aj_20200714 where SUBSTR(lilint_time from 1 for 4) like ?  group by aj_name_type  ";
        return getEntryList(sql, new SysHpXiaoFangAjTypeRowMapper(), new Object[]{"%" + year + "%"});
    }

    @Override
    public List<EveryYearSpotWarnCount> getAllDataRecordBackMax(String spotCode, String year) {
        String sql = "SELECT MAX(m.realtimeNumber) max,SUBSTR(createTime from 1 for 10) times,m.spotCode  FROM (select realtimeNumber,createTime,spotCode from sys_refined_crowdSituation where spotCode=? and realtimeNumber>'20000' and createTime like ?) m GROUP BY SUBSTR(createTime from 1 for 10)";
        return getEntryList(sql, new RowMapper<EveryYearSpotWarnCount>() {
            @Override
            public EveryYearSpotWarnCount mapRow(ResultSet rs, int rowNum) throws SQLException {
                EveryYearSpotWarnCount es = new EveryYearSpotWarnCount();
                es.setTimes(rs.getString("times"));
                es.setSpotid(rs.getString("spotCode"));
                es.setMax_number(rs.getString("max"));
                return es;
            }
        }, new Object[]{spotCode, "%" + year + "%"});
    }

    @Override
    public EveryYearSpotWarnCount getAllDataRecordBackCount(String spotCode, String year) {
        String sql = "SELECT COUNT(realtimeNumber) warn,count.spotCode,count.times from (select realtimeNumber,SUBSTR(createTime from 1 for 4) times,spotCode from sys_refined_crowdSituation_history where spotCode=? and realtimeNumber>20000 and SUBSTR(createTime from 1 for 4) =?) count group by count.times";
        return getEntry(sql, new RowMapper<EveryYearSpotWarnCount>() {
            @Override
            public EveryYearSpotWarnCount mapRow(ResultSet rs, int rowNum) throws SQLException {
                EveryYearSpotWarnCount es = new EveryYearSpotWarnCount();
                es.setTimes(rs.getString("times"));
                es.setSpotid(rs.getString("spotCode"));
                es.setWarn(rs.getString("warn"));
                return es;
            }
        }, new Object[]{spotCode, year});
    }

    @Override
    public List<SysHpRoadCondition> getSysHpRoadConditionBySpotCode(String spotCode) {
        String sql = "select * from hp_road_condition where spotCode=?";
        return getEntryList(sql, new SysHpRoadConditionMapper(), new Object[]{spotCode});
    }

    @Override
    public List<SysHpParkInfo> getParkInfoRealTimeData(String spotCode) throws ParseException {
//        List records = new ArrayList<>();
//        Map recordArr = new ConcurrentHashMap();
        String lastHour = DateUtil.getlastHour(0);
        String substring = lastHour.substring(0, lastHour.lastIndexOf(" "));
        String sub = "%" + substring + "%";
        //根据时间排序，获取最近时间的第一条数据
        String sql = "select f.*,p.* from hp_park_info f  left join hp_park p on f.parkId=p.parkId\n" +
                "WHERE f.updateTime like ? and p.spotCode=? order by updateTime desc ";
        List<SysHpParkInfo> entry = getEntryList(sql, new SysHpParkInfoRowMapper(), new Object[]{sub, spotCode});
        //根据当前时间获取前6小时数据
//        Map<String, Integer> map = new ConcurrentHashMap<>();

//        String getPastDataSql = "select id ,parkId,parkingNumber,recordTime,updateTime,total,total_left from hp_park_info where parkId=? and  recordTime like ?";
//        for (int i = 0; i < 6; i++) {
//            String lastHour = DateUtil.getlastHour(i);
//            String newlastHour="%"+lastHour+"%";
//            SysHpParkInfo entry1 = getEntry(getPastDataSql, new SysHpParkInfoRowMapper(), new Object[]{parkId, newlastHour});
//            int pastParkNumber = 0;
//            if (null != entry1) {
//                pastParkNumber = entry1.getParkingNumber();
//            }
//            String hour = DateUtil.getHour(lastHour + ":00:00");
//
//            map.put(hour, pastParkNumber);
//        }
//        recordArr.put("sum", entry);
////        recordArr.put("hours", map);
//        records.add(recordArr);
        return entry;
    }

    @Override
    public int getReworkPersonCount(String code) {
        String sql="select count(*) from hp_rework_person_resource where st_socialcreditcode=?";
        return queryInteger(sql,new Object[]{code});
    }

    @Override
    public List<ReworkInfo> getReworkInfo() {
        String sql=" SELECT\n" +
                "\tc.st_socialcreditcode,\n" +
                "\tc.st_enterprise_name,\n" +
                "\tc.dt_rework_time,\n" +
                "\tc.st_management_addr,\n" +
                "\tb.building_name,\n" +
                "\tp.count person\n" +
                "FROM\n" +
                "\t( hp_rework_company c  JOIN hp_rework_building b ON c.st_socialcreditcode = b.social_credit_code )\n" +
                "\t JOIN  (select st_socialcreditcode,st_enterprise_name,count(1) count from hp_rework_person_resource where st_audit_result='2' group by st_socialcreditcode) p ON c.st_socialcreditcode = p.st_socialcreditcode \n" +
                "WHERE  \n" +
                "\t c.st_audit_result = '2' \n" +
                "\tAND b.building_name != '未匹配' \n" +
                "\tAND c.dt_rework_time IS NOT NULL \n" +
                "\tAND b.building_name IS NOT NULL\n";
        return getEntryList(sql, new RowMapper<ReworkInfo>() {
            @Override
            public ReworkInfo mapRow(ResultSet resultSet, int i) throws SQLException {
                ReworkInfo re = new ReworkInfo();
                re.setName(resultSet.getString("st_enterprise_name"));
                re.setCode(resultSet.getString("st_socialcreditcode"));
                re.setBuilding(resultSet.getString("building_name"));
                re.setAddress(resultSet.getString("st_management_addr"));
                re.setReworkTime(resultSet.getString("dt_rework_time"));
                re.setReworkPerson(resultSet.getInt("person"));
                return re;
            }
        },new Object[]{});
    }

    /**
     * 添加短信
     */
    @Override
    public void addSms(HpSms hpSms){
        String sql = "insert into hp_sms (name, tel, message, create_time) values (?, ?, ?, NOW());";
        update(sql, new Object[]{hpSms.getName(), hpSms.getTel(), hpSms.getMessage()});
    }


    /**
     * 添加指令
     */
    @Override
    public void addInstruct(HpInstruct hpInstruct){
        String sql = "insert into hp_instruct (title, content, create_time, update_time) values (?,  ?, ?, ?);";
        update(sql, new Object[]{hpInstruct.getTitle(), hpInstruct.getContent(),new Date(), new Date()});
    }

    /**
     * 获取某时间段的最新数据
     * @param startDateTime
     * @param endDateTIme
     * @return
     */
    @Override
    public HpInstruct getInstruct(String startDateTime, String endDateTIme){
        String sql = "select id, title, content, create_time, update_time from hp_instruct where create_time BETWEEN  ? and ? order by create_time desc limit 1;";
        return getEntry(sql, new RowMapper<HpInstruct>() {
            @Override
            public HpInstruct mapRow(ResultSet resultSet, int i) throws SQLException {
                HpInstruct hpInstruct = new HpInstruct();
                hpInstruct.setId(resultSet.getInt("id"));
                hpInstruct.setTitle(resultSet.getString("title"));
                hpInstruct.setContent(resultSet.getString("content"));
                hpInstruct.setCreateTime(resultSet.getString("create_time").substring(0,resultSet.getString("create_time").length()-2));
                hpInstruct.setUpdateTime(resultSet.getString("update_time").substring(0,resultSet.getString("update_time").length()-2));
                return hpInstruct;
            }
        },new Object[]{startDateTime, endDateTIme});
    }

    @Override
    public List<HpInstruct> getInstructList(String startDateTime, String endDateTIme){
        String sql = "select id, title, content, create_time, update_time from hp_instruct where create_time BETWEEN  ? and ? order by create_time desc;";
        return getEntryList(sql, new RowMapper<HpInstruct>() {
            @Override
            public HpInstruct mapRow(ResultSet resultSet, int i) throws SQLException {
                HpInstruct hpInstruct = new HpInstruct();
                hpInstruct.setId(resultSet.getInt("id"));
                hpInstruct.setTitle(resultSet.getString("title"));
                hpInstruct.setContent(resultSet.getString("content"));
                hpInstruct.setCreateTime(resultSet.getString("create_time").substring(0,resultSet.getString("create_time").length()-2));
                hpInstruct.setUpdateTime(resultSet.getString("update_time").substring(0,resultSet.getString("update_time").length()-2));
                return hpInstruct;
            }
        },new Object[]{startDateTime, endDateTIme});
    }

    @Override
    public SysRefinedCrowdSituation getSpotDataByHour(String spotCode, String date) {
        String sql = "select * from sys_refined_crowdSituation_history where spotCode= ? and createTime <= ? order by createTime desc limit 1";
        return getEntry(sql, new SysRefinedCrowdSituationRowMapper(), new Object[]{ spotCode, date});
    }


}
